--创建外部表
create external table if not exists dongfeng.navinfo_stage_d005 (
        time                                              BIGINT              COMMENT '时间信息(采集时间)',
        longitude                                         INT                 COMMENT '经度',
        latitude                                          INT                 COMMENT '纬度',
        temperature                                       STRING              COMMENT '环境温度',
        atmosphericPressure                               STRING              COMMENT '大气压力',
        speed                                             STRING              COMMENT '车速',
        mile                                              STRING              COMMENT '总行驶里程',
        accelerator                                       STRING              COMMENT '油门踏板',
        handBrake                                         INT                 COMMENT '手刹状态',
        footBrake                                         INT                 COMMENT '脚刹状态',
        acc                                               INT                 COMMENT '点火状态',
        mil                                               INT                 COMMENT 'MIL灯状态',
        fuelLevel                                         STRING              COMMENT '油箱液位',
        intakeManifoldPressure                            INT                 COMMENT '进气歧管压力',
        intakeManifoldTemp                                INT                 COMMENT '进气歧管温度',
        oilPressuer                                       INT                 COMMENT '机油压力',
        voltage                                           INT                 COMMENT '蓄电池电压',
        totalFuelConsumption                              STRING              COMMENT '总油耗',
        ureaVoltage                                       INT                 COMMENT '尿素液位传感器电压',
        fuelTemp                                          INT                 COMMENT '燃油温度',
        coolantTemp                                       INT                 COMMENT '冷却液温度',
        intakeFlow                                        STRING              COMMENT '进气流量',
        intakeTemp                                        INT                 COMMENT '进气温度',
        realTimeOilConsumption                            INT                 COMMENT '瞬时油耗',
        engineTorMode                                     INT                 COMMENT '发动机扭矩模式',
        actualEnginePercentTor                            INT                 COMMENT '实际扭矩百分比',
        frictionEnginePercentTor                          INT                 COMMENT '摩擦扭矩百分比',
        idlingTor                                         INT                 COMMENT '怠速扭矩',
        actualInnerPercentTor                             INT                 COMMENT '实际内部扭矩',
        referenceTor                                      INT                 COMMENT '参考扭矩',
        rotation                                          STRING              COMMENT '发动机转速',
        realtimeFuelInjection                             STRING              COMMENT '实时喷油量',
        engineStartMode                                   STRING              COMMENT '发动机启动机模式',
        cumulativeRunningTime                             STRING              COMMENT '发动机总运行时间',
        cumulativeRotation                                INT                 COMMENT '发动机累计运转转数',
        activeLimitType                                   INT                 COMMENT '当前激活的限制类型',
        fuelQuality                                       INT                 COMMENT '燃油品质监测',
        ureaLevel                                         STRING              COMMENT '尿素箱液位(SCR)',
        ureaTemp                                          INT                 COMMENT '尿素箱温度(SCR)',
        actualBlueSpout                                   INT                 COMMENT '实际添蓝喷射量(SCR)',
        intakeOxynitrideValue                             INT                 COMMENT '进气氮氧测量值(SCR)',
        aftertreatOxynitrideContr                         STRING              COMMENT '后处理下游氮氧浓度(SCR)',
        totalUreaConsumption                              INT                 COMMENT '总尿素消耗(SCR)',
        scrSubsystemStatus                                INT                 COMMENT 'SCR子系统状态(SCR)',
        ureaPumpDutyfactor                                INT                 COMMENT '尿素泵占空比(SCR)',
        ureaSprayerDutyfactor                             INT                 COMMENT '尿素喷嘴占空比(SCR)',
        ureaLevelSignalCheck                              INT                 COMMENT '尿素液位信号校验(SCR)',
        aftertreatUpstreamExhaustTemp                     STRING              COMMENT '后处理上游排气温度(SCR)',
        catalystDownStreamTemp                            INT                 COMMENT '催化剂下游温度(SCR)',
        upstreamExhausTempSignalCheck                     INT                 COMMENT '上游排温信号校验(SCR)',
        aftertreatDewpointSts                             INT                 COMMENT '上游排温信号校验(SCR)',
        tailPipeOxynitrideDewPointSts                     INT                 COMMENT '尾管实时氮氧传感器露点状态(SCR)',
        noxSensorSignalCheck                              INT                 COMMENT 'Nox传感器信号校验(SCR)',
        intakeOxygenContent                               INT                 COMMENT '进气氧含量(DOC前氧)',
        exhaustOxygenContent                              INT                 COMMENT '排气氧含量(DOC前氧)',
        pumpRotation                                      INT                 COMMENT '泵转速',
        pumpStatus                                        INT                 COMMENT '泵工作状态',
        intakePressure                                    INT                 COMMENT '进气压力(DPF)',
        intakeTempPoc                                     INT                 COMMENT '进气温度(DPF)',
        exhaustPressure                                   INT                 COMMENT '排气背压(DPF/POC)',
        exhaustTemp                                       STRING              COMMENT '排气温度(DPF/POC)',
        carrierPressureDifferential                       INT                 COMMENT '载体压差(DPF/POC)',
        wasteGasQuantityFlow                              INT                 COMMENT '废气质量流量',
        aftertreatRealtimeInjection                       INT                 COMMENT '后处理的实时喷油量',
        tailpipeRealtimeOxynitrideContr                   INT                 COMMENT '尾管实时氮氧浓度',
        pemsNoxExhaust                                    INT                 COMMENT '基于PEMS的Nox排放值',
        ecuTamperDetection                                INT                 COMMENT 'ECU数据篡改监测',
        terminalId                                        INT                 COMMENT '终端号',
        serialNumber                                      INT                 COMMENT '流水号',
        part_time                                         INT                 COMMENT '分区时间'
) row format delimited fields terminated by '\001' STORED AS TEXTFILE location '/user/data/dongfeng/stage_import_d005';

--创建目标表
create table if not exists dongfeng.navinfo_parquet_d005(
        time                                              BIGINT              COMMENT '时间信息(采集时间)',
        longitude                                         INT                 COMMENT '经度',
        latitude                                          INT                 COMMENT '纬度',
        temperature                                       STRING              COMMENT '环境温度',
        atmosphericPressure                               STRING              COMMENT '大气压力',
        speed                                             STRING              COMMENT '车速',
        mile                                              STRING              COMMENT '总行驶里程',
        accelerator                                       STRING              COMMENT '油门踏板',
        handBrake                                         INT                 COMMENT '手刹状态',
        footBrake                                         INT                 COMMENT '脚刹状态',
        acc                                               INT                 COMMENT '点火状态',
        mil                                               INT                 COMMENT 'MIL灯状态',
        fuelLevel                                         STRING              COMMENT '油箱液位',
        intakeManifoldPressure                            INT                 COMMENT '进气歧管压力',
        intakeManifoldTemp                                INT                 COMMENT '进气歧管温度',
        oilPressuer                                       INT                 COMMENT '机油压力',
        voltage                                           INT                 COMMENT '蓄电池电压',
        totalFuelConsumption                              STRING              COMMENT '总油耗',
        ureaVoltage                                       INT                 COMMENT '尿素液位传感器电压',
        fuelTemp                                          INT                 COMMENT '燃油温度',
        coolantTemp                                       INT                 COMMENT '冷却液温度',
        intakeFlow                                        STRING              COMMENT '进气流量',
        intakeTemp                                        INT                 COMMENT '进气温度',
        realTimeOilConsumption                            INT                 COMMENT '瞬时油耗',
        engineTorMode                                     INT                 COMMENT '发动机扭矩模式',
        actualEnginePercentTor                            INT                 COMMENT '实际扭矩百分比',
        frictionEnginePercentTor                          INT                 COMMENT '摩擦扭矩百分比',
        idlingTor                                         INT                 COMMENT '怠速扭矩',
        actualInnerPercentTor                             INT                 COMMENT '实际内部扭矩',
        referenceTor                                      INT                 COMMENT '参考扭矩',
        rotation                                          STRING              COMMENT '发动机转速',
        realtimeFuelInjection                             STRING              COMMENT '实时喷油量',
        engineStartMode                                   STRING              COMMENT '发动机启动机模式',
        cumulativeRunningTime                             STRING              COMMENT '发动机总运行时间',
        cumulativeRotation                                INT                 COMMENT '发动机累计运转转数',
        activeLimitType                                   INT                 COMMENT '当前激活的限制类型',
        fuelQuality                                       INT                 COMMENT '燃油品质监测',
        ureaLevel                                         STRING              COMMENT '尿素箱液位(SCR)',
        ureaTemp                                          INT                 COMMENT '尿素箱温度(SCR)',
        actualBlueSpout                                   INT                 COMMENT '实际添蓝喷射量(SCR)',
        intakeOxynitrideValue                             INT                 COMMENT '进气氮氧测量值(SCR)',
        aftertreatOxynitrideContr                         STRING              COMMENT '后处理下游氮氧浓度(SCR)',
        totalUreaConsumption                              INT                 COMMENT '总尿素消耗(SCR)',
        scrSubsystemStatus                                INT                 COMMENT 'SCR子系统状态(SCR)',
        ureaPumpDutyfactor                                INT                 COMMENT '尿素泵占空比(SCR)',
        ureaSprayerDutyfactor                             INT                 COMMENT '尿素喷嘴占空比(SCR)',
        ureaLevelSignalCheck                              INT                 COMMENT '尿素液位信号校验(SCR)',
        aftertreatUpstreamExhaustTemp                     STRING              COMMENT '后处理上游排气温度(SCR)',
        catalystDownStreamTemp                            INT                 COMMENT '催化剂下游温度(SCR)',
        upstreamExhausTempSignalCheck                     INT                 COMMENT '上游排温信号校验(SCR)',
        aftertreatDewpointSts                             INT                 COMMENT '上游排温信号校验(SCR)',
        tailPipeOxynitrideDewPointSts                     INT                 COMMENT '尾管实时氮氧传感器露点状态(SCR)',
        noxSensorSignalCheck                              INT                 COMMENT 'Nox传感器信号校验(SCR)',
        intakeOxygenContent                               INT                 COMMENT '进气氧含量(DOC前氧)',
        exhaustOxygenContent                              INT                 COMMENT '排气氧含量(DOC前氧)',
        pumpRotation                                      INT                 COMMENT '泵转速',
        pumpStatus                                        INT                 COMMENT '泵工作状态',
        intakePressure                                    INT                 COMMENT '进气压力(DPF)',
        intakeTempPoc                                     INT                 COMMENT '进气温度(DPF)',
        exhaustPressure                                   INT                 COMMENT '排气背压(DPF/POC)',
        exhaustTemp                                       STRING              COMMENT '排气温度(DPF/POC)',
        carrierPressureDifferential                       INT                 COMMENT '载体压差(DPF/POC)',
        wasteGasQuantityFlow                              INT                 COMMENT '废气质量流量',
        aftertreatRealtimeInjection                       INT                 COMMENT '后处理的实时喷油量',
        tailpipeRealtimeOxynitrideContr                   INT                 COMMENT '尾管实时氮氧浓度',
        pemsNoxExhaust                                    INT                 COMMENT '基于PEMS的Nox排放值',
        ecuTamperDetection                                INT                 COMMENT 'ECU数据篡改监测',
        terminalId                                        INT                 COMMENT '终端号',
        serialNumber                                      INT                 COMMENT '流水号',
        create_time                                       STRING              COMMENT '数据创建时间'
) partitioned by (part_time int) stored as Parquet TBLPROPERTIES('parquet.compression'='SNAPPY');
